﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Data;
using System.Data.SqlClient;

namespace OrmAuto
{

		public abstract class ABaseSql
		{
				DbProviderFactory factory =
							DbProviderFactories.GetFactory("System.Data.SqlClient");

				public ABaseSql()
				{

				}

				public DbCommand getSqlCommand(string commandText, CommandType ctype, DbConnection Conn)
				{
						if (!tryToOpenConn(Conn)) return null;
						var cmd = Conn.CreateCommand();
						cmd.CommandText = commandText;//"AdventureWorksEntities.GetOrderDetails"
						cmd.CommandType = ctype;
						//EntityParameter param = new EntityParameter();
						//param.Value = "43659";
						//param.ParameterName = "SalesOrderHeaderId";
						//cmd.Parameters.Add(param);

						return cmd;
				} //fM

				public DbDataReader exeqSQLReader(string commandText, CommandType ctype, DbConnection Conn)
				{
						using (var com = getSqlCommand(commandText, ctype, Conn))
						{
								return com.ExecuteReader();
						}
				}//fM

				public DataTable exeqSQLTable(string commandText, CommandType ctype, DbConnection Conn)
				{
						using (var com = getSqlCommand(commandText, ctype, Conn))
						{
								DataTable dt = new DataTable();
								dt.Load(com.ExecuteReader());
								return dt;
						}
				}//fM

				public int exeqSQLNoRet(string commandText, CommandType ctype, DbConnection Conn)
				{
						using (var com = getSqlCommand(commandText, ctype, Conn))
						{
								return com.ExecuteNonQuery();
						}
				}//fM

				public int exeqSQLNoRet(string commandText, CommandType ctype, string pConnStringName)
				{
						using (var conn = getSQLConnection(pConnStringName))
						{
								using (var com = getSqlCommand(commandText, ctype, conn))
								{
										return com.ExecuteNonQuery();
								}
						}
				}//fM

				public DataTable execStoredProc(string pSProcedure, DbParameter[] pParametri )
		 {
		  DataTable dt = new DataTable();
				using( var command =
						getSqlCommand(pSProcedure, CommandType.StoredProcedure,getSQLConnection("baseConnectionString")) )
						{					
								command.Parameters.AddRange(pParametri);   
								  
								dt.Load(command.ExecuteReader( CommandBehavior.CloseConnection));        	
						}
						return dt;
		 }//fM

				public DbConnection getSQLConnection(string connStringNAME)
				{
						string cs = System.Configuration.ConfigurationManager.ConnectionStrings[connStringNAME].ConnectionString;
						DbConnection conn = factory.CreateConnection();
						conn.ConnectionString = cs;
						return conn;
				}//fM

				public bool tryToOpenConn(IDbConnection Conn)
				{
						if (Conn == null) return false;
						if (Conn.State == ConnectionState.Open) return true;
						try
						{
								Conn.Open();
								return true;
						}
						catch (Exception ex)
						{
								return false;
						}
				}//fM




		}
}
